package com.lyons.generate.service.impl;

import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.google.common.base.CaseFormat;
import com.lyons.generate.pojo.ColumnClass;
import com.lyons.generate.pojo.TableClass;
import com.lyons.generate.util.DBUtils;

public class CodeGenerateTest {

	/**
	 * 
	 * @param list   需要生成代码的列表
	 * @param prefix true 去掉前缀
	 * @throws Exception
	 */
	public void generate(List<TableClass> list, String path) throws Exception {

		// 根据表名查询表描述,实体类名
		List<TableClass> array = init(list);

		// 根据表名查询数所有字段信息生成实体类信息
		array = selectEntity(array);

		// 生成代码
		new FreemarkerGenerateCodeService().generateCode(list, path);
	}

	/**
	 * 根据表名查询出所有字段(生成实体类)
	 * 
	 * @param list
	 * @return
	 * @throws SQLException
	 */
	public List<TableClass> selectEntity(List<TableClass> list) throws SQLException {

		Connection connection = DBUtils.getConnection();
		DatabaseMetaData metaData = connection.getMetaData();

		for (TableClass tb : list) {

			// 获得表中的所有列
			ResultSet columns = metaData.getColumns(connection.getCatalog(), null, tb.getTableName(), null);

			// 获取主键
			ResultSet primaryKeys = metaData.getPrimaryKeys(connection.getCatalog(), null, tb.getTableName());

			List<ColumnClass> columnClassList = new ArrayList<>();
			while (columns.next()) {

				String columnName = columns.getString("COLUMN_NAME");
				String typeName = columns.getString("TYPE_NAME");
				String remarks = columns.getString("REMARKS");

				ColumnClass columnClass = ColumnClass.builder().build();

				// 表字段名
				columnClass.setColumnName(columnName);
				// 字段数据类型
				columnClass.setType(getTypeName(typeName));
				// 字段描述
				columnClass.setRemark(remarks);
				// 根据字段生成实体类属性名称
				columnClass.setPropertyName(CaseFormat.LOWER_UNDERSCORE.to(CaseFormat.UPPER_CAMEL, columnName));

				// 遍历主键判断列是否是主键
				while (primaryKeys.next()) {

					String pkName = primaryKeys.getString("COLUMN_NAME");
					if (columnName.equals(pkName)) {
						columnClass.setPrimary(true);
					}
					primaryKeys.first();
				}

				columnClassList.add(columnClass);
			}

			tb.setColumnList(columnClassList);

		}
		return list;
	}

	/**
	 * 数据库类型转为java类型
	 * 
	 * @param typeName
	 * @return
	 */
	public String getTypeName(String typeName) {

		if (typeName.equalsIgnoreCase("INT") || typeName.equalsIgnoreCase("tinyint")) {

			return "Integer";
		} else if (typeName.equalsIgnoreCase("bigint")) {

			return "String";
		} else if (typeName.equalsIgnoreCase("varchar") || typeName.equalsIgnoreCase("text") || typeName.equalsIgnoreCase("char")) {

			return "String";
		} else if (typeName.equalsIgnoreCase("datetime") || typeName.equalsIgnoreCase("timestamp") || typeName.equalsIgnoreCase("date")) {

			return "Date";
		} else if (typeName.equalsIgnoreCase("time")) {

			return "Date";
		} else if (typeName.equalsIgnoreCase("BIT")) {

			return "Boolean";
		} else if (typeName.equalsIgnoreCase("DOUBLE")) {

			return "Double";
		}
		return "String";
	}

	/**
	 * 根据提供的表信息查询表描述和生成对应的实体类名称
	 * 
	 * @throws SQLException
	 */
	public List<TableClass> init(List<TableClass> list) throws SQLException {

		Connection connection = DBUtils.getConnection();

		for (TableClass table : list) {

			Statement stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery("SHOW CREATE TABLE " + table.getTableName());
			if (rs != null && rs.next()) {

				// DDL 语句
				String create = rs.getString(2);
				String comment = parse(create);

				table.setRemark(comment);
				if (table.getPrefix()) {

					table.setClassName(CaseFormat.LOWER_UNDERSCORE.to(CaseFormat.UPPER_CAMEL, table.getTableName().substring(table.getTableName().indexOf("_") + 1)));
				} else {

					table.setClassName(CaseFormat.LOWER_UNDERSCORE.to(CaseFormat.UPPER_CAMEL, table.getTableName()));
				}
			}
			rs.close();
		}
		return list;
	}

	/**
	 * 解析出表的描述
	 */
	private String parse(String all) {

		String comment = null;
		int index = all.indexOf("COMMENT='");
		if (index < 0) {
			return "";
		}
		comment = all.substring(index + 9);
		comment = comment.substring(0, comment.length() - 1);
		try {
			comment = new String(comment.getBytes("utf-8"));
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}
		return comment;
	}

}